package com.sinping.qh.service.admin.impl;

import com.google.common.collect.Lists;
import com.ibm.icu.text.Collator;
import com.sinping.qh.api.support.Const;
import com.sinping.qh.api.support.ConstEum;
import com.sinping.qh.api.support.FrontPage;
import com.sinping.qh.api.support.ResDto;
import com.sinping.qh.domain.auth.Role;
import com.sinping.qh.domain.common.BuildingDomain;
import com.sinping.qh.domain.common.UnitDomain;
import com.sinping.qh.domain.lab.LabInspectItemRelationDomain;
import com.sinping.qh.domain.lab.LabInspectItemTreeDomain;
import com.sinping.qh.domain.lab.LaboratoryDomain;
import com.sinping.qh.domain.records.CheckRecord;
import com.sinping.qh.domain.user.UserLaboratory;
import com.sinping.qh.dto.admin.ApiBuildingDto;
import com.sinping.qh.dto.admin.ApiUnitDto;
import com.sinping.qh.dto.admin.lab.AddCompanyPaymentErrorDetailDTO;
import com.sinping.qh.dto.admin.lab.LabImportExcelDto;
import com.sinping.qh.dto.admin.lab.LaboratoryDto;
import com.sinping.qh.dto.auth.UserSessionDTO;
import com.sinping.qh.query.lab.LaboratoryQuery;
import com.sinping.qh.query.lab.UserLaboratoryQuery;
import com.sinping.qh.repository.mongo.BuildingReposity;
import com.sinping.qh.repository.mongo.UnitRepository;
import com.sinping.qh.repository.mongo.lab.LabInspectItemRelationRepository;
import com.sinping.qh.repository.mongo.lab.LabInspectItemTreeRepository;
import com.sinping.qh.repository.mongo.lab.LaboratoryRepository;
import com.sinping.qh.repository.mongo.user.UserLaboratoryRepository;
import com.sinping.qh.service.MongoManager;
import com.sinping.qh.service.admin.ILaboratoryService;
import com.sinping.qh.service.auth.IRoleService;
import com.sinping.qh.service.auth.ISessionService;
import com.sinping.qh.service.common.CommonDbConst;
import com.sinping.qh.service.common.FileManagerService;
import com.sinping.qh.utils.excel.CreateCSV;
import com.sinping.qh.utils.excel.POIUtil;
import com.sinping.qh.utils.file.ByteFile;
import com.sinping.qh.utils.mapper.BeanMapper;
import com.sinping.qh.utils.mapper.JsonMapper;
import com.sinping.qh.utils.misc.IdGenerator;
import com.sinping.qh.utils.time.DateFormatUtil;
import ma.glasnost.orika.impl.util.StringUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.domain.*;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.aggregation.Aggregation;
import org.springframework.data.mongodb.core.aggregation.AggregationOperation;
import org.springframework.data.mongodb.core.aggregation.AggregationResults;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.data.mongodb.core.query.Update;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.*;

/**
 * @author jiahuaiqing
 * @create 2018/6/1
 */
@Service
public class LaboratoryServiceImpl implements ILaboratoryService {
    @Value("${qh.upload.filePath}")
    String filePath;

    @Autowired
    private FileManagerService commonService;

    @Autowired
    LaboratoryRepository laboratoryRepository;

    @Autowired
    private LabInspectItemRelationRepository labInspectItemRelationRepository;

    @Autowired
    private UserLaboratoryRepository userLaboratoryRepository;

    @Autowired
    UnitRepository unitRepository;

    @Autowired
    BuildingReposity buildingReposity;

    @Autowired
    MongoManager mongoManager;

    @Qualifier("sessionServiceRedisImpl")
    @Autowired
    ISessionService iSessionService;
    @Autowired
    private IRoleService roleService;

    @Autowired
    LabInspectItemTreeRepository labInspectItemTreeRepository;

    @Override
    public ResDto addLaboratory(HttpServletRequest request, LaboratoryDto laboratoryDto) {
        try {
            String token = request.getHeader(Const.TOKEN_NAME);
            UserSessionDTO userSessionDTO = iSessionService.getSessionByToken(token);
            if (userSessionDTO == null) {
                return new ResDto(Const.PARAM_ERROR, "用户未登录");
            }
            //验证参数
            if (StringUtils.isBlank(userSessionDTO.getId())) {
                return new ResDto(Const.SERVER_ERROR, "获取用户id失败");
            }

            LaboratoryDomain laboratoryDomain = BeanMapper.map(laboratoryDto, LaboratoryDomain.class);
            laboratoryDomain.setId(IdGenerator.uuid2());
            laboratoryDomain.setCreateUserId(userSessionDTO.getId());
            //laboratoryDomain.setUpdateUserId("aaa");
            laboratoryDomain.setGmtCreated(new Date());
            laboratoryDomain.setGmtModified(new Date());
            laboratoryDomain.setStatus("0");
            LaboratoryDomain save = laboratoryRepository.save(laboratoryDomain);
            if (save == null) {
                return new ResDto(Const.SERVER_ERROR, "新增实验室失败");
            }
            return new ResDto(Const.SUCCESS, "新增实验室成功");
        } catch (Exception e) {
            ResDto resDto = new ResDto();
            resDto.setStatus(Const.SERVER_ERROR);
            resDto.setMsg("数据对象转换异常");
            return resDto;
        }

    }

    @Override
    public ResDto updateLaboratory(HttpServletRequest request, LaboratoryDto laboratoryDto) {
        try {
            String token = request.getHeader(Const.TOKEN_NAME);
            UserSessionDTO userSessionDTO = iSessionService.getSessionByToken(token);
            if (userSessionDTO == null) {
                return new ResDto(Const.PARAM_ERROR, "用户未登录");
            }
            //验证参数
            if (StringUtils.isBlank(userSessionDTO.getId())) {
                return new ResDto(Const.SERVER_ERROR, "获取用户id失败");
            }

            LaboratoryDomain domain = laboratoryRepository.findOne(laboratoryDto.getId());
            if (domain == null) {
                return new ResDto(Const.SERVER_ERROR, "id不存在");
            }
            domain.setLabName(laboratoryDto.getLabName());
            domain.setUnitName(laboratoryDto.getUnitName());
            domain.setUnitId(laboratoryDto.getUnitId());
            domain.setBuildingId(laboratoryDto.getBuildingId());
            domain.setBuildingName(laboratoryDto.getBuildingName());
            domain.setRoomNo(laboratoryDto.getRoomNo());
            domain.setRoomArea(laboratoryDto.getRoomArea());
            domain.setRiskLevel(laboratoryDto.getRiskLevel());
            domain.setRiskManager(laboratoryDto.getRiskManager());
            domain.setTelephone(laboratoryDto.getTelephone());
            domain.setOffices(laboratoryDto.getOffices());
            domain.setUpdateUserId(userSessionDTO.getId());
            domain.setGmtModified(new Date());
            LaboratoryDomain save = laboratoryRepository.save(domain);
            if (save == null) {
                return new ResDto(Const.SERVER_ERROR, "实验室修改失败");
            }

            //同步修改检查计划里的roomNo
            //条件
            Criteria condition = Criteria.where("check_lab_id").is(domain.getId());
            Query query = new Query(condition);
            //更新值
            Update update = new Update();
            update.set("check_lab", domain.getBuildingName());
            update.set("check_room", domain.getRoomNo());
            update.set("offices", domain.getOffices());
            update.set("gmt_modified", new Date());
            //任务
            mongoManager.getTemplate().updateMulti(query, update, CheckRecord.class);

            return new ResDto(Const.SUCCESS, "实验室修改成功");
        } catch (Exception e) {
            e.printStackTrace();
            ResDto resDto = new ResDto();
            resDto.setStatus(Const.SERVER_ERROR);
            resDto.setMsg("数据对象转换异常");
            return resDto;
        }
    }

    @Override
    public ResDto getLaboratoryById(String id) {
        try {
            LaboratoryDomain domain = laboratoryRepository.findOne(id);
            if (domain == null) {
                return new ResDto(Const.SERVER_ERROR, "实验室id不存在");
            }
            LaboratoryDto laboratoryDto = BeanMapper.map(domain, LaboratoryDto.class);
            String laboratoryDtoJson = JsonMapper.defaultMapper().toJson(laboratoryDto);
            ResDto resDto = new ResDto(Const.SUCCESS, "成功");
            resDto.setDatas(laboratoryDtoJson);
            return resDto;
        } catch (Exception e) {
            e.printStackTrace();
            ResDto resDto = new ResDto();
            resDto.setStatus(Const.SERVER_ERROR);
            resDto.setMsg("数据异常");
            return resDto;
        }
    }

    /**
     * 实验室分页列表
     * 逻辑：先只查配置实验室的labid +（只查院系的labid-相同院系又配置了实验室的labid），最后的labid在实验表中进行查询
     *
     * @param request
     * @param frontpage
     * @return
     */
    @Override
    public ResDto getLaboratoryFrontPage(HttpServletRequest request, FrontPage frontpage) {
        /**
         * 获取当前用户id
         */
        String token = request.getHeader(Const.TOKEN_NAME);
        UserSessionDTO userSessionDTO = iSessionService.getSessionByToken(token);
        if (userSessionDTO == null) {
            return new ResDto(Const.PARAM_ERROR, "用户未登录");
        }
        //验证参数
        if (StringUtils.isBlank(userSessionDTO.getId())) {
            return new ResDto(Const.SERVER_ERROR, "获取用户id失败");
        }


        List<String> allLabId = new ArrayList<>();

        //只查配置-负责实验室
        List<LaboratoryDto> laboratoryDto = userManagerLabConfig(userSessionDTO.getId(), frontpage);

        //只查配置负责院系[院系下的所有实验室]----注意：(减去配置负责实验室的的院系)
        List<LaboratoryDto> laboratoryDtos_unit = userManagerUnitConfig(userSessionDTO.getId(), frontpage);

        if (null != laboratoryDto && laboratoryDto.size() > 0) {
            for (int i = 0; i < laboratoryDto.size(); i++) {
                allLabId.add(laboratoryDto.get(i).getId());
            }
        }

        if (null != laboratoryDtos_unit && laboratoryDtos_unit.size() > 0) {
            if (allLabId.size() > 0) {
                for (int i = 0; i < laboratoryDtos_unit.size(); i++) {
                    if (!allLabId.contains(laboratoryDtos_unit.get(i).getId())) {
                        allLabId.add(laboratoryDtos_unit.get(i).getId());
                    }
                }
            } else {
                for (int i = 0; i < laboratoryDtos_unit.size(); i++) {
                    allLabId.add(laboratoryDtos_unit.get(i).getId());
                }
            }
        }

        Integer pageSize = frontpage.getSize();
        Integer pageNum = frontpage.getPage();

        LaboratoryQuery query;
        Map<String, Object> conditionMap = frontpage.getCondition();
        if (null == conditionMap || conditionMap.isEmpty()) {
            //查询全部数据
            query = new LaboratoryQuery();
        } else {
            //按条件查询
            String conditionMapJson = JsonMapper.defaultMapper().toJson(conditionMap);
            LaboratoryDto laboratory_Dto = JsonMapper.defaultMapper().fromJson(conditionMapJson, LaboratoryDto.class);
            query = BeanMapper.map(laboratory_Dto, LaboratoryQuery.class);
        }

        //设置查询出来的labid
        query.setLabIDList(allLabId);

        try {
            Pageable pageable = new PageRequest(pageNum - 1, pageSize, new Sort(Sort.Direction.DESC, "gmt_created"));
            Page<LaboratoryDomain> laboratoryListPage = mongoManager.findPage(query, pageable, LaboratoryDomain.class);

            List<LaboratoryDto> laboratoryDtoList = BeanMapper.mapList(laboratoryListPage.getContent(), LaboratoryDto.class);


            UserLaboratoryQuery userLaboratoryQuery = new UserLaboratoryQuery();
            userLaboratoryQuery.setType(CommonDbConst.USER_MANAGER_LAB);
            List<UserLaboratory> userLaboratories = mongoManager.find(userLaboratoryQuery, UserLaboratory.class);
            for (LaboratoryDto laboratorydto : laboratoryDtoList) {
                StringBuffer security = new StringBuffer();
                StringBuffer telephone = new StringBuffer();
                for (int i = 0; i < userLaboratories.size(); i++) {
                    if (userLaboratories.get(i).getManagerLabId().equals(laboratorydto.getId())) {
                        if (!StringUtils.isEmpty(userLaboratories.get(i).getRoleId())) {
                            Role role = roleService.selectById(userLaboratories.get(i).getRoleId());
                            if (role != null) {
                                if (CommonDbConst.SECURITY_YES.equals(role.getSecurity())) {
                                    if (userLaboratories.size() == 1) {
                                        security.append(userLaboratories.get(i).getUserName());
                                        telephone.append(userLaboratories.get(i).getTelephone());
                                    } else {
                                        if (security.toString().length() == 0) {
                                            security.append(userLaboratories.get(i).getUserName());
                                            telephone.append(userLaboratories.get(i).getTelephone());
                                        } else {
                                            security.append("," + userLaboratories.get(i).getUserName());
                                            telephone.append("," + userLaboratories.get(i).getTelephone());
                                        }

                                    }
                                }
                            }
                        }

                    }
                }
                laboratorydto.setSecurity(security.toString());
                laboratorydto.setSecurityPhone(telephone.toString());

            }

            ResDto resDto = new ResDto(ConstEum.SUCCESS);
            resDto.setMsg("成功");
            long totalPages = laboratoryListPage.getTotalPages();
            int currentPage = frontpage.getPage();
            frontpage = new FrontPage();
            frontpage.setPage(currentPage);
            frontpage.setTotal((int) laboratoryListPage.getTotalElements());
            frontpage.setPages((int) totalPages);
            frontpage.setRecords(laboratoryDtoList);
            resDto.setDatas(JsonMapper.defaultMapper().toJson(frontpage));

            return resDto;
        } catch (Exception e) {
            e.printStackTrace();
            return new ResDto(Const.SERVER_ERROR, "数据异常");
        }

    }

    @Autowired
    private MongoTemplate template;

    //负责院系(除去)
    public List<LaboratoryDto> userManagerUnitConfig(String userSessionDTO_id, FrontPage frontpage) {

        Integer pageSize = frontpage.getSize();
        Integer pageNum = frontpage.getPage();

        LaboratoryQuery query;
        Map<String, Object> conditionMap = frontpage.getCondition();
        if (null == conditionMap || conditionMap.isEmpty()) {
            //查询全部数据
            query = new LaboratoryQuery();
        } else {
            //按条件查询
            String conditionMapJson = JsonMapper.defaultMapper().toJson(conditionMap);
            LaboratoryDto laboratoryDto = JsonMapper.defaultMapper().fromJson(conditionMapJson, LaboratoryDto.class);
            query = BeanMapper.map(laboratoryDto, LaboratoryQuery.class);
        }

        /**获取用户管理的所有院系的id(减去在配置实验室时，与配置院系相同的,实验室所在的院系的id)**/
        List<String> onlyUnitIdList = unitIDHandler(userSessionDTO_id);
        if (onlyUnitIdList == null || onlyUnitIdList.size() == 0) {
            return null;//说明没有配置院系或者配置的院系和实验室的相同
        }

        /**属于当前登录用户负责院系的院系id*/
        if (query.getUnitId() == null) {
            query.setUnitIDList(onlyUnitIdList);
        }

        List<ApiBuildingDto> userManagerLabInBuilding = getUserManagerLabAndUnitInBuilding(userSessionDTO_id);

        List<String> buildingIdList = new ArrayList<>();
        for (ApiBuildingDto apiBuildingDto : userManagerLabInBuilding) {
            buildingIdList.add(apiBuildingDto.getId());
        }
        /**属于当前登录用户负责实验室所在的楼宇 + 负责院系下所有实验室的所在楼宇id*/
        if (query.getBuildingId() == null) {
            query.setBuildingIDList(buildingIdList);
        }

        /**院系/实验室有效*/
        query.setStatus(CommonDbConst.DB_DELETE_NORMAL);

        try {
            List<AggregationOperation> operations = Lists.newArrayList();

            //添加权限
            Criteria mainCriteria = new Criteria();
            Criteria statusCri = Criteria.where("status").is(query.getStatus());
            Criteria unitCri = null, buildingCri = null;
            if (onlyUnitIdList != null && onlyUnitIdList.size() > 0) {
                unitCri = Criteria.where("unit_id").in(onlyUnitIdList);
            }
            if (buildingIdList != null && buildingIdList.size() > 0) {
                buildingCri = Criteria.where("building_id").in(buildingIdList);
            }
            if (unitCri == null && buildingCri == null) {
                mainCriteria.andOperator(statusCri);
            }
            if (unitCri != null && buildingCri == null) {
                mainCriteria.andOperator(statusCri, unitCri);
            }
            if (unitCri == null && buildingCri != null) {
                mainCriteria.andOperator(statusCri, buildingCri);
            }
            if (unitCri != null && buildingCri != null) {
                mainCriteria.andOperator(statusCri, unitCri, buildingCri);
            }

            operations.add(Aggregation.match(mainCriteria));
            Aggregation aggregation = Aggregation.newAggregation(operations);
            String table = template.getCollectionName(LaboratoryDomain.class);
            AggregationResults<LaboratoryDto> results = template.aggregate(aggregation, table, LaboratoryDto.class);

            return results.getMappedResults();
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }

    }


    //用户配置了实验室的查询
    public List<LaboratoryDto> userManagerLabConfig(String userSessionDTO_id, FrontPage frontpage) {
        Integer pageSize = frontpage.getSize();
        Integer pageNum = frontpage.getPage();

        UserLaboratoryQuery query;
        Map<String, Object> conditionMap = frontpage.getCondition();
        if (null == conditionMap || conditionMap.isEmpty()) {
            //查询全部数据
            query = new UserLaboratoryQuery();
        } else {
            //按条件查询
            String conditionMapJson = JsonMapper.defaultMapper().toJson(conditionMap);
            LaboratoryDto laboratoryDto = JsonMapper.defaultMapper().fromJson(conditionMapJson, LaboratoryDto.class);
            query = BeanMapper.map(laboratoryDto, UserLaboratoryQuery.class);
        }


        /**当前登录用户*/
        query.setUserId(userSessionDTO_id);
        /**院系/实验室有效*/
        query.setStatus(CommonDbConst.DB_DELETE_NORMAL);
        query.setType(CommonDbConst.USER_MANAGER_LAB);

        List<ApiUnitDto> userManagerLabInUnit = getUserManagerLabInUnit(userSessionDTO_id);

        List<String> unitIdList = new ArrayList<>();
        for (ApiUnitDto apiUnitDto : userManagerLabInUnit) {
            unitIdList.add(apiUnitDto.getId());
        }
        /**属于当前登录用户负责实验室所在的院系*/
        if (query.getUnitId() == null) {
            query.setUnitIdList(unitIdList);
        }

        List<ApiBuildingDto> userManagerLabInBuilding = getUserManagerLabInBuilding(userSessionDTO_id);

        List<String> buildingIdList = new ArrayList<>();
        for (ApiBuildingDto apiBuildingDto : userManagerLabInBuilding) {
            buildingIdList.add(apiBuildingDto.getId());
        }
        /**属于当前登录用户负责实验室所在的楼宇*/
        if (query.getBuildingId() == null) {
            query.setBuildingIdList(buildingIdList);
        }

        try {
            List<UserLaboratory> userLaboratoriesList = mongoManager.find(query, UserLaboratory.class);

            List<LaboratoryDomain> laboratoryDomainList = new ArrayList<>();
            for (UserLaboratory userLaboratory : userLaboratoriesList) {
                LaboratoryDomain one = laboratoryRepository.findOne(userLaboratory.getManagerLabId());
                if (one != null) {
                    laboratoryDomainList.add(one);
                }

            }
            List<LaboratoryDto> laboratoryDtoList = BeanMapper.mapList(laboratoryDomainList, LaboratoryDto.class);
            return laboratoryDtoList;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public ResDto deleteLaboratoryById(HttpServletRequest request, String id) {

        try {
            String token = request.getHeader(Const.TOKEN_NAME);
            UserSessionDTO userSessionDTO = iSessionService.getSessionByToken(token);
            if (userSessionDTO == null) {
                return new ResDto(Const.PARAM_ERROR, "用户未登录");
            }
            //验证参数
            if (StringUtils.isBlank(userSessionDTO.getId())) {
                return new ResDto(Const.SERVER_ERROR, "获取用户id失败");
            }
            LaboratoryDomain laboratoryDomain = laboratoryRepository.findOne(id);
            if (laboratoryDomain == null) {
                return new ResDto(Const.SERVER_ERROR, "实验室id不存在");
            }

            laboratoryRepository.delete(id);
            return new ResDto(Const.SUCCESS, "删除成功");
        } catch (Exception e) {
            e.printStackTrace();
            return new ResDto(Const.SERVER_ERROR, "删除实验室信息异常");
        }
    }

    @Override
    public ResDto deleteLaboratoryByIds(HttpServletRequest request, List<String> ids) {

        try {
            String token = request.getHeader(Const.TOKEN_NAME);
            UserSessionDTO userSessionDTO = iSessionService.getSessionByToken(token);
            if (userSessionDTO == null) {
                return new ResDto(Const.PARAM_ERROR, "用户未登录");
            }
            //验证参数
            if (StringUtils.isBlank(userSessionDTO.getId())) {
                return new ResDto(Const.SERVER_ERROR, "获取用户id失败");
            }
            for (String idOne:ids) {
                LaboratoryDomain laboratoryDomain = laboratoryRepository.findOne(idOne);
                if (laboratoryDomain == null) {
                    return new ResDto(Const.SERVER_ERROR, "实验室id:" + idOne + "不存在");
                }
                laboratoryDomain.setStatus(CommonDbConst.DB_DELETE_LOGICAL);
                LaboratoryDomain save = laboratoryRepository.save(laboratoryDomain);
                //改变（逻辑删除）用户负责表中该实验室有效状态
                //用户删除实验室时，直接把用户实验室表中的记录状态修改为status:1
                for (String id : ids) {
                    List<UserLaboratory> allByManagerUnitIdList = userLaboratoryRepository.findAllByManagerUnitIdAndType(id, CommonDbConst.USER_MANAGER_LAB);
                    for (UserLaboratory userLaboratory : allByManagerUnitIdList) {
                        userLaboratory.setStatus(CommonDbConst.DB_DELETE_LOGICAL);
                    }
                    userLaboratoryRepository.save(allByManagerUnitIdList);
                    //当数据删除实验室时，给配置的检查项也的删除
                    labInspectItemRelationRepository.deleteByLabId(id);
                }

            }
            return new ResDto(Const.SUCCESS, "删除成功");
        } catch (Exception e) {
            e.printStackTrace();
            return new ResDto(Const.SERVER_ERROR, "删除实验室信息异常");
        }
    }

    /**
     * 导出实验室列表（按条件）
     *
     * @param frontPage
     * @param response
     * @return
     */
    @Override
    public ResDto laboratoryExport(FrontPage frontPage, HttpServletResponse response, HttpServletRequest request) {
        /**
         * 获取当前用户id
         */
        String token = request.getHeader(Const.TOKEN_NAME);
        UserSessionDTO userSessionDTO = iSessionService.getSessionByToken(token);
        if (userSessionDTO == null) {
            return new ResDto(Const.PARAM_ERROR, "用户未登录");
        }
        //验证参数
        if (StringUtils.isBlank(userSessionDTO.getId())) {
            return new ResDto(Const.SERVER_ERROR, "获取用户id失败");
        }


        List<String> allLabId = new ArrayList<>();

        //配置负责实验室
        List<LaboratoryDto> laboratoryDto = userManagerLabConfig(userSessionDTO.getId(), frontPage);

        //配置负责院系(除去配置负责实验室的)
        List<LaboratoryDto> laboratoryDtos_unit = userManagerUnitConfig(userSessionDTO.getId(), frontPage);

        if (null != laboratoryDto && laboratoryDto.size() > 0) {
            for (int i = 0; i < laboratoryDto.size(); i++) {
                allLabId.add(laboratoryDto.get(i).getId());
            }
        }

        if (null != laboratoryDtos_unit && laboratoryDtos_unit.size() > 0) {
            if (allLabId.size() > 0) {
                for (int i = 0; i < laboratoryDtos_unit.size(); i++) {
                    if (!allLabId.contains(laboratoryDtos_unit.get(i).getId())) {
                        allLabId.add(laboratoryDtos_unit.get(i).getId());
                    }
                }
            } else {
                for (int i = 0; i < laboratoryDtos_unit.size(); i++) {
                    allLabId.add(laboratoryDtos_unit.get(i).getId());
                }
            }
        }

        Integer pageSize = frontPage.getSize();
        Integer pageNum = frontPage.getPage();

        LaboratoryQuery query;
        Map<String, Object> conditionMap = frontPage.getCondition();
        if (null == conditionMap || conditionMap.isEmpty()) {
            //查询全部数据
            query = new LaboratoryQuery();
        } else {
            //按条件查询
            String conditionMapJson = JsonMapper.defaultMapper().toJson(conditionMap);
            LaboratoryDto laboratory_Dto = JsonMapper.defaultMapper().fromJson(conditionMapJson, LaboratoryDto.class);
            query = BeanMapper.map(laboratory_Dto, LaboratoryQuery.class);
        }

        //设置查询出来的labid,导出的数据不用分页
        query.setLabIDList(allLabId);

        try {
            List<LaboratoryDomain> laboratoryDomainsList = mongoManager.find(query, LaboratoryDomain.class, new Sort(Sort.Direction.DESC, "gmt_created"));

            List<LaboratoryDto> laboratoryDtoList = BeanMapper.mapList(laboratoryDomainsList, LaboratoryDto.class);

            //id -> item name
            Map<String, String> labInspectIdName = new HashMap<>();
            //id -> item level
            Map<String, String> labInspectIdLevel = new HashMap<>();
            //id -> item parent id
            Map<String, String> labInspectIdParentId = new HashMap<>();

            UserLaboratoryQuery userLaboratoryQuery = new UserLaboratoryQuery();
            userLaboratoryQuery.setType(CommonDbConst.USER_MANAGER_LAB);
            List<UserLaboratory> userLaboratories = mongoManager.find(userLaboratoryQuery, UserLaboratory.class);
            for (LaboratoryDto laboratorydto : laboratoryDtoList) {
                StringBuffer security = new StringBuffer();
                StringBuffer telephone = new StringBuffer();
                for (int i = 0; i < userLaboratories.size(); i++) {
                    if (userLaboratories.get(i).getManagerLabId().equals(laboratorydto.getId())) {
                        if (!StringUtils.isEmpty(userLaboratories.get(i).getRoleId())) {
                            Role role = roleService.selectById(userLaboratories.get(i).getRoleId());
                            if (role != null) {
                                if (CommonDbConst.SECURITY_YES.equals(role.getSecurity())) {
                                    if (userLaboratories.size() == 1) {
                                        security.append(userLaboratories.get(i).getUserName());
                                        telephone.append(userLaboratories.get(i).getTelephone());
                                    } else {
                                        if (security.toString().length() == 0) {
                                            security.append(userLaboratories.get(i).getUserName());
                                            telephone.append(userLaboratories.get(i).getTelephone());
                                        } else {
                                            security.append("," + userLaboratories.get(i).getUserName());
                                            telephone.append("," + userLaboratories.get(i).getTelephone());
                                        }

                                    }
                                }
                            }
                        }


                    }
                }
                laboratorydto.setSecurity(security.toString());
                laboratorydto.setSecurityPhone(telephone.toString());

                //查询危险源
                List<LabInspectItemRelationDomain> byLabId = labInspectItemRelationRepository.findByLabIdOrderByGmtCreatedDesc(laboratorydto.getId());
                List<String> first = new ArrayList<>();
                Map<String, List<String>> second = new HashMap<>();
                if(!byLabId.isEmpty()){
                    for (LabInspectItemRelationDomain labInspectItemRelationDomain : byLabId){
                        String inspectItemId = labInspectItemRelationDomain.getInspectItemId();
                        if(!labInspectIdName.containsKey(inspectItemId)){
                            LabInspectItemTreeDomain item = labInspectItemTreeRepository.findOne(inspectItemId);
                            labInspectIdName.put(inspectItemId, item.getItemName());
                            labInspectIdLevel.put(inspectItemId, item.getItemLevel());
                            labInspectIdParentId.put(inspectItemId, item.getItemParentId());
                        }

                        //再查检查项表
                        String itemName = labInspectIdName.get(inspectItemId);
                        if (labInspectIdLevel.get(inspectItemId).equals("1")) {
                            first.add(inspectItemId);
                            if(!second.containsKey(inspectItemId)){
                                second.put(inspectItemId, new ArrayList<>());
                            }
                        } else {
                            if(second.containsKey(labInspectIdParentId.get(inspectItemId))){
                                second.get(labInspectIdParentId.get(inspectItemId)).add(0, itemName);
                            }else{
                                List<String> l = new ArrayList<>();
                                l.add(itemName);
                                second.put(labInspectIdParentId.get(inspectItemId), l);
                            }
                        }
                    }

                    StringBuffer buffer = new StringBuffer();
                    if (first.size() > 0) {
                        for (int i = 0; i < first.size(); i++) {
                            String firstId = first.get(i);
                            buffer.append(labInspectIdName.get(firstId));
                            if(second.get(firstId) == null){
                                System.out.println(firstId);
                            }
                            if(!second.get(firstId).isEmpty()){
                                buffer.append("：");
                                for (int j = 0; j < second.get(firstId).size(); j++) {
                                    String secondName = second.get(firstId).get(j);
                                    buffer.append(secondName);
                                    if (j + 1 < second.get(firstId).size()) {
                                        buffer.append("、");
                                    }
                                }
                            }
                            if(i + 1 < first.size()){
                                buffer.append("；");
                            }else{
                                buffer.append("。");
                            }
                        }
                    }
                    laboratorydto.setLabInspectItemNameStrs(buffer.toString());
                }
            }

            Date date = new Date();
            String exportDate = DateFormatUtil.formatDate("yyyy-MM-dd_HHmm", date);
            String[] head = {"实验室名称", "所属单位", "所属楼宇", "所属所室", "房间号", "房间面积(m2)", "安全负责人", "联系电话", "安全级别", "安全员", "安全员电话", "危险源"};
            String[] content = {"labName", "unitName", "buildingName", "offices", "roomNo/text", "roomArea", "riskManager", "telephone/text", "riskLevel", "security", "securityPhone", "labInspectItemNameStrs"};
            CreateCSV.exportCsv(head, content, laboratoryDtoList, LaboratoryDto.class, exportDate + "实验室导出表.xls", response);
            ResDto resDto = new ResDto();
            resDto.setDatas("");
            resDto.setMsg("导出成功");
            resDto.setStatus(Const.SUCCESS);
            return resDto;
        } catch (Exception e) {
            e.printStackTrace();
            return new ResDto(Const.SERVER_ERROR, "数据异常");
        }
    }

    @Override
    public ResDto labImportExcel(LabImportExcelDto labImportExcelDto, HttpServletRequest request) {
        try {
            String token = request.getHeader(Const.TOKEN_NAME);
            UserSessionDTO userSessionDTO = iSessionService.getSessionByToken(token);
            if (userSessionDTO == null) {
                return new ResDto(Const.PARAM_ERROR, "用户未登录");
            }
            //验证参数
            if (StringUtils.isBlank(userSessionDTO.getId())) {
                return new ResDto(Const.SERVER_ERROR, "获取用户id失败");
            }

            //本地测试用的
            //byte[] bytes = ByteFile.getBytes("E:/123.xls");
            //服务器中的绝对路径
            //byte[] bytes = ByteFile.getBytes("/var/www/images/qh/file/1/2018-07-13/4740a6311c234716bef1b5295f072161.xls");

            //File file =new File(fileOrImageUrlById);
            //String fileOrImageUrlById = filePath + commonService.getFileOrImageUrlById(labImportExcelDto.getFileId());
            String xlsfileOrImageUrl ="/var/www/images/qh/file/1/2018/8a4dc660c16187b18ed65a8ed9ebd09e.xls";
            InputStream is = new FileInputStream(
                    java.text.Normalizer.normalize(xlsfileOrImageUrl, java.text.Normalizer.Form.NFKD)
            );
            byte[] bytes = ByteFile.getBytes(is);
            if (bytes == null) {
                return new ResDto(Const.PARAM_ERROR, "找不到文件");
            }

            //Map<String, Object> readExcelResult = POIUtil.readExcel(bytes, fileOrImageUrlById);
            Map<String, Object> readExcelResult = POIUtil.readExcel(bytes,
                    java.text.Normalizer.normalize(xlsfileOrImageUrl, java.text.Normalizer.Form.NFKD)
            );
            if (readExcelResult.isEmpty()) {
                return new ResDto(Const.PARAM_ERROR, "读取Excel失败");
            }
            //有错误啦：必填项为空
            List<AddCompanyPaymentErrorDetailDTO> errorDetailDTO = (List<AddCompanyPaymentErrorDetailDTO>) readExcelResult.get("addCompanyPaymentErrorDetailDTOS");
            if (null != errorDetailDTO && errorDetailDTO.size() > 0) {
                String errorCode = errorDetailDTO.get(0).getErrorCode();
                if (StringUtils.isNotBlank(errorCode)) {
                    return new ResDto(Const.PARAM_ERROR, "Excel导入异常:" + errorCode);
                }
                return new ResDto(Const.PARAM_ERROR, "Excel导入异常");
            }
            List<LaboratoryDomain> labDomainList = (List<LaboratoryDomain>) readExcelResult.get("labDomainList");
            if (null == labDomainList || labDomainList.size() == 0) {
                return new ResDto(Const.PARAM_ERROR, "读取Excel数据为空");
            }
            StringBuffer exceptionStr = new StringBuffer();
            for (int i = 0; i < labDomainList.size(); i++) {
                LaboratoryDomain laboratoryDomain = labDomainList.get(i);
                if (StringUtils.isBlank(laboratoryDomain.getUnitName())) {
                    laboratoryDomain = null;
                    exceptionStr.append("第" + (i + 1) + "行单位名称不能为空;");
                }
                if (StringUtils.isBlank(laboratoryDomain.getBuildingName())) {
                    laboratoryDomain = null;
                    exceptionStr.append("第" + (i + 1) + "行楼宇名称不能为空;");
                }
                if (StringUtils.isBlank(laboratoryDomain.getRoomNo())) {
                    laboratoryDomain = null;
                    exceptionStr.append("第" + (i + 1) + "行房间号不能为空;");
                }
                //去重复(楼宇+房间号来判断是否重复) 2019-3-18 水哥说去掉此判断
                /*if (null != laboratoryDomain) {
                    List<LaboratoryDomain> byBuildingNameAndRoomNo = laboratoryRepository.findByBuildingNameAndRoomNoAndStatus(laboratoryDomain.getBuildingName(), laboratoryDomain.getRoomNo(), CommonDbConst.DB_DELETE_NORMAL);
                    if (null != byBuildingNameAndRoomNo && byBuildingNameAndRoomNo.size() > 0) {
                        //说明有重复,为null,不让去添加入库
                        laboratoryDomain = null;
                        exceptionStr.append("第" + (i + 1) + "行数据重复;");
                    }
                }*/

                if (null != laboratoryDomain) {
                    List<UnitDomain> allByUnitNameAndStatus = unitRepository.findAllByUnitNameAndStatus(laboratoryDomain.getUnitName(), CommonDbConst.DB_DELETE_NORMAL);
                    if (null != allByUnitNameAndStatus && allByUnitNameAndStatus.size() > 0) {
                        laboratoryDomain.setUnitId(allByUnitNameAndStatus.get(0).getId());
                    } else {
                        laboratoryDomain = null;
                        exceptionStr.append("第" + (i + 1) + "行所属单位不存在;");
                        continue;
                    }
                }

                if (null != laboratoryDomain) {
                    List<BuildingDomain> allByBuildingNameAndStatus = buildingReposity.findAllByBuildingNameAndStatus(laboratoryDomain.getBuildingName(), CommonDbConst.DB_DELETE_NORMAL);
                    if (null != allByBuildingNameAndStatus && allByBuildingNameAndStatus.size() > 0) {
                        laboratoryDomain.setBuildingId(allByBuildingNameAndStatus.get(0).getId());
                    } else {
                        laboratoryDomain = null;
                        exceptionStr.append("第" + (i + 1) + "行所属楼宇不存在;");
                        continue;
                    }
                }

                if (null != laboratoryDomain) {
                    laboratoryDomain.setId(IdGenerator.uuid2());
                    laboratoryDomain.setStatus("0");
                    laboratoryDomain.setCreateUserId(userSessionDTO.getId());
                    laboratoryDomain.setGmtModified(new Date());
                    laboratoryDomain.setGmtCreated(new Date());
                    laboratoryRepository.save(laboratoryDomain);
                }
            }
            int length = exceptionStr.length();
            if (length > 0) {
                return new ResDto(Const.SUCCESS, exceptionStr.toString());
            } else {
                return new ResDto(Const.SUCCESS, "Excel导入成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
            return new ResDto(Const.PARAM_ERROR, "Excel导入异常");
        }

    }

    @Override
    public ResDto getManagerUnitList(HttpServletRequest request) {

        /**
         * 获取当前用户id
         */
        String token = request.getHeader(Const.TOKEN_NAME);
        UserSessionDTO userSessionDTO = iSessionService.getSessionByToken(token);
        if (userSessionDTO == null) {
            return new ResDto(Const.PARAM_ERROR, "操作用户未登录");
        }

        //验证参数
        if (StringUtils.isBlank(userSessionDTO.getId())) {

            return new ResDto(Const.SERVER_ERROR, "获取操作用户id失败");
        }

        List<ApiUnitDto> userManagerLabInUnit = getUserManagerLabInUnit(userSessionDTO.getId());

        //按院系名称-汉字排序
        Collections.sort(userManagerLabInUnit, new Comparator<ApiUnitDto>() {
            @Override
            public int compare(ApiUnitDto o1, ApiUnitDto o2) {
                Comparator<Object> com = Collator.getInstance(Locale.CHINA);
                return com.compare(o1.getUnitName(), o2.getUnitName());
            }
        });


        String data = JsonMapper.defaultMapper().toJson(userManagerLabInUnit);

        return new ResDto(ConstEum.SUCCESS, data);
    }

    /**
     * 获取用户负责实验室所在楼宇+院系所在楼宇列表
     */
    @Override
    public ResDto getManagerBuildingList(HttpServletRequest request) {

        /**
         * 获取当前用户id
         */
        String token = request.getHeader(Const.TOKEN_NAME);
        UserSessionDTO userSessionDTO = iSessionService.getSessionByToken(token);
        if (userSessionDTO == null) {
            return new ResDto(Const.PARAM_ERROR, "操作用户未登录");
        }

        //验证参数
        if (StringUtils.isBlank(userSessionDTO.getId())) {

            return new ResDto(Const.SERVER_ERROR, "获取操作用户id失败");
        }

        List<ApiBuildingDto> userManagerLabInBuilding = getUserManagerLabAndUnitInBuilding(userSessionDTO.getId());


        //按楼宇名称-汉字排序
        Collections.sort(userManagerLabInBuilding, new Comparator<ApiBuildingDto>() {
            @Override
            public int compare(ApiBuildingDto o1, ApiBuildingDto o2) {
                Comparator<Object> com = Collator.getInstance(Locale.CHINA);
                return com.compare(o1.getBuildingName(), o2.getBuildingName());
            }
        });

        String data = JsonMapper.defaultMapper().toJson(userManagerLabInBuilding);

        return new ResDto(ConstEum.SUCCESS, data);
    }

    /**
     * 用户负责的实验室所在的院系 和 用户负责的院系
     */
    public List<ApiUnitDto> getUserManagerLabInUnit(String userId) {

        List<UnitDomain> unitList = new ArrayList<>();
        List<String> unitIdList = new ArrayList<>();

        //用户负责的实验室所在的院系 和 用户负责的院系
        List<UserLaboratory> userManagerUnitList = userLaboratoryRepository.findAllByUserIdAndStatusOrderByGmtCreatedDesc(userId, CommonDbConst.DB_DELETE_NORMAL);

        for (UserLaboratory userLaboratory : userManagerUnitList) {
            if (!unitIdList.contains(userLaboratory.getManagerUnitId())) {
                if (userLaboratory.getManagerUnitId() != null) {
                    unitIdList.add(userLaboratory.getManagerUnitId());
                    //进行了排序：且这个不好用，用了三方的Collator
                    UnitDomain one = unitRepository.findByIdAndStatusOrderByUnitNameAsc(userLaboratory.getManagerUnitId(), CommonDbConst.DB_DELETE_NORMAL);
                    if (one != null) {
                        unitList.add(one);
                    }

                }

            }

        }

        List<ApiUnitDto> unitDtoList = BeanMapper.mapList(unitList, ApiUnitDto.class);
        return unitDtoList;
    }

    /**
     * 用户负责的实验室所在的楼宇 和 用户负责院系的所有楼宇
     */
    public List<ApiBuildingDto> getUserManagerLabAndUnitInBuilding(String userId) {
        //List<ApiBuildingDto> labAndUnitInBuildingList = new ArrayList<>();
        List<String> labAndUnitInBuildingIDS = new ArrayList<>();

        //用户负责的实验室所在的楼宇
        List<ApiBuildingDto> userManagerLabInBuilding = getUserManagerLabInBuilding(userId);
        for (ApiBuildingDto buildingDto : userManagerLabInBuilding) {
            labAndUnitInBuildingIDS.add(buildingDto.getId());
        }
        //用户负责院系的所有楼宇
        List<ApiBuildingDto> userManagerLabAndUnitInBuilding = getUserManagerUnitInBuilding(userId);
        for (ApiBuildingDto buildingsDto : userManagerLabAndUnitInBuilding) {
            if (!labAndUnitInBuildingIDS.contains(buildingsDto.getId())) {
                labAndUnitInBuildingIDS.add(buildingsDto.getId());
            }
        }

        //为了去重复和对楼宇名称进行排序(拿到楼宇的所有id,在楼宇表中进行查询)
        Iterable<BuildingDomain> labAndUnitInBuildings = buildingReposity.findAll(labAndUnitInBuildingIDS);
        List<ApiBuildingDto> labAndUnitInBuildingList = BeanMapper.mapList(labAndUnitInBuildings, ApiBuildingDto.class);

        return labAndUnitInBuildingList;
    }


    /**
     * 用户负责的实验室所在的楼宇
     */
    public List<ApiBuildingDto> getUserManagerLabInBuilding(String userId) {

        List<BuildingDomain> buildngList = new ArrayList<>();
        List<String> buildingIdList = new ArrayList<>();

        List<UserLaboratory> userManagerBuildingList = userLaboratoryRepository.findAllByUserIdAndTypeOrderByGmtCreatedDesc(userId, CommonDbConst.USER_MANAGER_LAB);
        for (UserLaboratory userLaboratory : userManagerBuildingList) {
            if (!buildingIdList.contains(userLaboratory.getLabBuildingId())) {
                if (userLaboratory.getLabBuildingId() != null) {
                    buildingIdList.add(userLaboratory.getLabBuildingId());
                    BuildingDomain one = buildingReposity.findOne(userLaboratory.getLabBuildingId());
                    if (one != null) {
                        buildngList.add(one);
                    }
                }
            }
        }

        List<ApiBuildingDto> builidingDtoList = BeanMapper.mapList(buildngList, ApiBuildingDto.class);
        return builidingDtoList;
    }

    /**
     * 用户负责院系的所有楼宇(除去在配置实验室时，与配置院系相同的实验室所在的院系)
     * 获取用户管理院系的所有楼宇(除去在配置实验室时，与配置院系相同的实验室所在的院系)
     */
    private List<ApiBuildingDto> getUserManagerUnitInBuilding(String userId) {

        List<String> onlyUnitIdLis = unitIDHandler(userId);

        //根据院系id查出该院系下的所有实验室
        List<LaboratoryDomain> labLists = new ArrayList<>();
        for (String unitID : onlyUnitIdLis) {
            List<LaboratoryDomain> labDomainList = laboratoryRepository.findAllByUnitIdAndStatus(unitID, CommonDbConst.DB_DELETE_NORMAL);
            labLists.addAll(labDomainList);
        }

        //楼宇id集合
        List<String> buildingIdList = new ArrayList<>();
        List<BuildingDomain> buildngList = new ArrayList<>();
        for (LaboratoryDomain lab : labLists) {
            if (!buildingIdList.contains(lab.getBuildingId())) {
                if (lab.getBuildingId() != null) {
                    buildingIdList.add(lab.getBuildingId());
                    BuildingDomain one = buildingReposity.findOne(lab.getBuildingId());
                    if (one != null) {
                        buildngList.add(one);
                    }
                }

            }
        }

        List<ApiBuildingDto> builidingDtoList = BeanMapper.mapList(buildngList, ApiBuildingDto.class);
        return builidingDtoList;
    }

    /**
     * 获取用户管理的所有院系的id(减去在配置实验室时，与配置院系相同的,实验室所在的院系的id)
     */
    private List<String> unitIDHandler(String userId) {
        //用户负责的实验室所在的院系
        List<UserLaboratory> userManagerUnitList_lab = userLaboratoryRepository.findAllByUserIdAndTypeAndStatusOrderByGmtCreatedDesc(userId, CommonDbConst.USER_MANAGER_LAB, CommonDbConst.DB_DELETE_NORMAL);
        //用户负责的院系
        List<UserLaboratory> userManagerUnitList_unit = userLaboratoryRepository.findAllByUserIdAndTypeAndStatusOrderByGmtCreatedDesc(userId, CommonDbConst.USER_MANAGER_UNIT, CommonDbConst.DB_DELETE_NORMAL);

        //只是负责院系的院系id集合：且去了重复(即配置院系又配置相同院系的实验室的，院系id)
        List<String> onlyUnitIdLis = new ArrayList<>();

        List<String> lab_unitIdList = new ArrayList<>();

        if (userManagerUnitList_lab.size() > 0) {
            for (UserLaboratory userLaboratory : userManagerUnitList_lab) {
                if (!lab_unitIdList.contains(userLaboratory.getManagerUnitId())) {
                    lab_unitIdList.add(userLaboratory.getManagerUnitId());
                }
            }
            for (UserLaboratory userLaboratory : userManagerUnitList_unit) {
                if (!onlyUnitIdLis.contains(userLaboratory.getManagerUnitId())) {
                    onlyUnitIdLis.add(userLaboratory.getManagerUnitId());
                }
            }
            for (String unitID : lab_unitIdList) {
                //在负责院系的院系id集合中删除,实验室中相同的院系id
                onlyUnitIdLis.remove(unitID);
            }

        } else {
            for (UserLaboratory userLaboratory : userManagerUnitList_unit) {
                onlyUnitIdLis.add(userLaboratory.getManagerUnitId());
            }
        }

        return onlyUnitIdLis;
    }
}
